package com.ducquoc.examreviewdegreea1;

import java.util.ArrayList;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DocDatabase extends Activity {
	// Ten databse
	private static final String DATABASE_NAME = "ExamReviewDegreeA1";

	// Version database
	private static int DATABASE_VERSION;

	//Co tat ca 4 bang
	// BANG tbl_theory. Ten table va cac column trong bang
	private static final String TBL_THEORY = "tbl_theory";
	private static final String COLUMN_ID_THEORY = "id";
	private static final String COLUMN_ASK_THEORY = "ask";
	private static final String COLUMN_ANS1_THEORY = "ans1";
	private static final String COLUMN_ANS2_THEORY = "ans2";
	private static final String COLUMN_ANS3_THEORY = "ans3";
	private static final String COLUMN_ANS4_THEORY = "ans4";
	private static final String COLUMN_TRUEANS_THEORY = "trueans";
	private static final String COLUMN_HIDE_THEORY = "hide";
	
	// BANG tbl_sign. Ten table va cac column trong bang
	private static final String TBL_SIGN = "tbl_sign";
	private static final String COLUMN_ID_SIGN = "id";
	private static final String COLUMN_ASK_SIGN = "ask";
	private static final String COLUMN_IMAGE_SIGN = "img";
	private static final String COLUMN_ANS1_SIGN = "ans1";
	private static final String COLUMN_ANS2_SIGN = "ans2";
	private static final String COLUMN_ANS3_SIGN = "ans3";
	private static final String COLUMN_ANS4_SIGN = "ans4";
	private static final String COLUMN_TRUEANS_SIGN = "trueans";
	private static final String COLUMN_HIDE_SIGN = "hide";
	
	// BANG tbl_circumstance. Ten table va cac column trong bang
	private static final String TBL_CIRCUMSTANCE = "tbl_circumstance";
	private static final String COLUMN_ID_CIRCUMSTANCE = "id";
	private static final String COLUMN_ASK_CIRCUMSTANCE = "ask";
	private static final String COLUMN_IMAGE_CIRCUMSTANCE = "img";
	private static final String COLUMN_ANS1_CIRCUMSTANCE = "ans1";
	private static final String COLUMN_ANS2_CIRCUMSTANCE = "ans2";
	private static final String COLUMN_ANS3_CIRCUMSTANCE = "ans3";
	private static final String COLUMN_ANS4_CIRCUMSTANCE = "ans4";
	private static final String COLUMN_TRUEANS_CIRCUMSTANCE = "trueans";
	private static final String COLUMN_HIDE_CIRCUMSTANCE = "hide";
	
	// BANG tbl_update. Ten table va cac column trong bang
	private static final String TBL_UPDATE = "tbl_update";
	private static final String COLUMN_ID_UPDATE = "id";
	private static final String COLUMN_VERSION_UPDATE = "version";
	private static final String COLUMN_DATE_UPDATE = "date";
	private static final String COLUMN_DIRECT_UPDATE = "direct";

	// Cac doi tuong khac
	private static Context context;
	static SQLiteDatabase db;
	private OpenHelper openHelper;

	DocPojo docPojo = null;
	ArrayList<DocPojo> arr = new ArrayList<DocPojo>();
	MainActivity contextMain = null;

	// Ham dung, khoi tao doi tuong
	public DocDatabase(Context c) {
		DocDatabase.context = c;
	}

	// Ham mo ket noi toi database va nhan version tu MainActivity
	public DocDatabase open(int version) throws SQLException {
		DATABASE_VERSION = version;
		openHelper = new OpenHelper(context);
		db = openHelper.getWritableDatabase();
		return this;
	}

	// Ham dong ket noi toi database
	public void close() {
		openHelper.close();
	}
	
	// Ham createData dung de chen du lieu moi vao cho bang tbl_theory
	public long createDataTheory(int _id, String _ask, String _ans1,
			String _ans2, String _ans3, String _ans4, String _trueans, int _hide) {
		ContentValues cv = new ContentValues();
		cv.put(COLUMN_ID_THEORY, _id);
		cv.put(COLUMN_ASK_THEORY, _ask);
		cv.put(COLUMN_ANS1_THEORY, _ans1);
		cv.put(COLUMN_ANS2_THEORY, _ans2);
		cv.put(COLUMN_ANS3_THEORY, _ans3);
		cv.put(COLUMN_ANS4_THEORY, _ans4);
		cv.put(COLUMN_TRUEANS_THEORY, _trueans);
		cv.put(COLUMN_HIDE_THEORY, _hide);
		return db.insert(TBL_THEORY, null, cv);
	}

	// Ham createData dung de chen du lieu moi vao cho bang tbl_sign
	public long createDataSign(int _id, String _ask, String _img, String _ans1,
			String _ans2, String _ans3, String _ans4, String _trueans, int _hide) {
		ContentValues cv = new ContentValues();
		cv.put(COLUMN_ID_SIGN, _id);
		cv.put(COLUMN_ASK_SIGN, _ask);
		cv.put(COLUMN_IMAGE_SIGN, _img);
		cv.put(COLUMN_ANS1_SIGN, _ans1);
		cv.put(COLUMN_ANS2_SIGN, _ans2);
		cv.put(COLUMN_ANS3_SIGN, _ans3);
		cv.put(COLUMN_ANS4_SIGN, _ans4);
		cv.put(COLUMN_TRUEANS_SIGN, _trueans);
		cv.put(COLUMN_HIDE_SIGN, _hide);
		return db.insert(TBL_SIGN, null, cv);
	}
	
	// Ham createData dung de chen du lieu moi vao cho bang tbl_circumstance
	public long createDataCircumstance(int _id, String _ask, String _img, String _ans1,
			String _ans2, String _ans3, String _ans4, String _trueans, int _hide) {
		ContentValues cv = new ContentValues();
		cv.put(COLUMN_ID_CIRCUMSTANCE, _id);
		cv.put(COLUMN_ASK_CIRCUMSTANCE, _ask);
		cv.put(COLUMN_IMAGE_CIRCUMSTANCE, _img);
		cv.put(COLUMN_ANS1_CIRCUMSTANCE, _ans1);
		cv.put(COLUMN_ANS2_CIRCUMSTANCE, _ans2);
		cv.put(COLUMN_ANS3_CIRCUMSTANCE, _ans3);
		cv.put(COLUMN_ANS4_CIRCUMSTANCE, _ans4);
		cv.put(COLUMN_TRUEANS_CIRCUMSTANCE, _trueans);
		cv.put(COLUMN_HIDE_CIRCUMSTANCE, _hide);
		return db.insert(TBL_CIRCUMSTANCE, null, cv);
	}
	
	// Ham createData dung de chen du lieu moi vao cho bang tbl_update
	public long createDataUpdate(int _id, int _version, String _date, String _direct) {
		ContentValues cv = new ContentValues();
		cv.put(COLUMN_ID_UPDATE, _id);
		cv.put(COLUMN_VERSION_UPDATE, _version);
		cv.put(COLUMN_DATE_UPDATE, _date);
		cv.put(COLUMN_DIRECT_UPDATE, _direct);
		return db.insert(TBL_UPDATE, null, cv);
	}
	
	//Ham cap nhat lai version trong bang tbl_update
    public boolean updateVersion(int idUpdate, int versionNew){
        ContentValues cv = new ContentValues();
        cv.put(COLUMN_VERSION_UPDATE, versionNew);
        long kq = db.update(TBL_UPDATE,cv,COLUMN_ID_UPDATE+"='"+idUpdate+"'", null);
        if (kq==0)
            return false;
        else
            return true;
    }
	
	// Ham getDataTheory tra ve toan bo du lieu cua tbl_theory
	// Chuyen sang dang mang khi custom listview
	public ArrayList<DocPojo> getDataTheory() {
		String[] columns = new String[] { COLUMN_ID_THEORY, COLUMN_ASK_THEORY,
				COLUMN_ANS1_THEORY, COLUMN_ANS2_THEORY, COLUMN_ANS3_THEORY, COLUMN_ANS4_THEORY,
				COLUMN_TRUEANS_THEORY, COLUMN_HIDE_THEORY };
		Cursor c = db.query(TBL_THEORY, columns, null, null, null, null, null);
		// getColumnIndex(COLUMN_ID_THEORY); la lay chi so, vi tri cua cot COLUMN_ID_THEORY
		// ...
		int iID = c.getColumnIndex(COLUMN_ID_THEORY);
		int iA = c.getColumnIndex(COLUMN_ASK_THEORY);
		int iA1 = c.getColumnIndex(COLUMN_ANS1_THEORY);
		int iA2 = c.getColumnIndex(COLUMN_ANS2_THEORY);
		int iA3 = c.getColumnIndex(COLUMN_ANS3_THEORY);
		int iA4 = c.getColumnIndex(COLUMN_ANS4_THEORY);
		int iT = c.getColumnIndex(COLUMN_TRUEANS_THEORY);
		int iH = c.getColumnIndex(COLUMN_HIDE_THEORY);

		// Vong lap lay du lieu cua con tro
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) 
		{
			docPojo = new DocPojo();
			docPojo.setId(c.getInt(iID));
			docPojo.setAsk(c.getString(iA));
			docPojo.setAns1(c.getString(iA1));
			docPojo.setAns2(c.getString(iA2));
			docPojo.setAns3(c.getString(iA3));
			docPojo.setAns4(c.getString(iA4));
			docPojo.setTrueans(c.getString(iT));
			docPojo.setHide(c.getInt(iH));

			arr.add(docPojo);

		}
		c.close();
		return arr;
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	//Ham lay du lieu theo dieu kien where
	public ArrayList<DocPojo> getRowDataTheory(int idTheory) {
		String[] columns = new String[] { COLUMN_ASK_THEORY,
				COLUMN_ANS1_THEORY, COLUMN_ANS2_THEORY, COLUMN_ANS3_THEORY, COLUMN_ANS4_THEORY, COLUMN_TRUEANS_THEORY, };
		//Cursor c = db.query(TBL_THEORY, columns, null, null, null, null, null);
		
		String selectQuery = "select ask, ans1, ans2, ans3, ans4, trueans" +
				" from tbl_theory where id = " + idTheory + "";
		Cursor c = db.rawQuery(selectQuery, null);
		int iA = c.getColumnIndex(COLUMN_ASK_THEORY);
		int iA1 = c.getColumnIndex(COLUMN_ANS1_THEORY);
		int iA2 = c.getColumnIndex(COLUMN_ANS2_THEORY);
		int iA3 = c.getColumnIndex(COLUMN_ANS3_THEORY);
		int iA4 = c.getColumnIndex(COLUMN_ANS4_THEORY);
		int iT = c.getColumnIndex(COLUMN_TRUEANS_THEORY);
		//int iH = c.getColumnIndex(COLUMN_HIDE_THEORY);
		
		// Vong lap lay du lieu cua con tro
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) 
		{
			docPojo = new DocPojo();
			//docPojo.setId(c.getInt(iID));
			docPojo.setAsk(c.getString(iA));
			docPojo.setAns1(c.getString(iA1));
			docPojo.setAns2(c.getString(iA2));
			docPojo.setAns3(c.getString(iA3));
			docPojo.setAns4(c.getString(iA4));
			docPojo.setTrueans(c.getString(iT));
			//docPojo.setHide(c.getInt(iH));

			arr.add(docPojo);
		}
		c.close();
		return arr;
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	// Ham getDataSign tra ve toan bo du lieu cua tbl_sign
	// Chuyen sang dang mang khi custom listview
	public ArrayList<DocPojo> getDataSign() {
		String[] columns = new String[] { COLUMN_ID_SIGN, COLUMN_ASK_SIGN, COLUMN_IMAGE_SIGN,
				COLUMN_ANS1_SIGN, COLUMN_ANS2_SIGN, COLUMN_ANS3_SIGN, COLUMN_ANS4_SIGN,
				COLUMN_TRUEANS_SIGN, COLUMN_HIDE_SIGN };
		Cursor c = db.query(TBL_SIGN, columns, null, null, null, null, null);
		// getColumnIndex(COLUMN_ID_SIGN); la lay chi so, vi tri cua cot COLUMN_ID_SIGN
		// ...
		int iID = c.getColumnIndex(COLUMN_ID_SIGN);
		int iA = c.getColumnIndex(COLUMN_ASK_SIGN);
		int iI = c.getColumnIndex(COLUMN_IMAGE_SIGN);
		int iA1 = c.getColumnIndex(COLUMN_ANS1_SIGN);
		int iA2 = c.getColumnIndex(COLUMN_ANS2_SIGN);
		int iA3 = c.getColumnIndex(COLUMN_ANS3_SIGN);
		int iA4 = c.getColumnIndex(COLUMN_ANS4_SIGN);
		int iT = c.getColumnIndex(COLUMN_TRUEANS_SIGN);
		int iH = c.getColumnIndex(COLUMN_HIDE_SIGN);

		// Vong lap lay du lieu cua con tro
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) 
		{
			docPojo = new DocPojo();
			docPojo.setId(c.getInt(iID));
			docPojo.setAsk(c.getString(iA));
			docPojo.setImg(c.getString(iI));
			docPojo.setAns1(c.getString(iA1));
			docPojo.setAns2(c.getString(iA2));
			docPojo.setAns3(c.getString(iA3));
			docPojo.setAns4(c.getString(iA4));
			docPojo.setTrueans(c.getString(iT));
			docPojo.setHide(c.getInt(iH));

			arr.add(docPojo);

		}
		c.close();
		return arr;
	}

	// Ham getDataCircumstance tra ve toan bo du lieu cua tbl_circumstance
	// Chuyen sang dang mang khi custom listview
	public ArrayList<DocPojo> getDataCircumstance() {
		String[] columns = new String[] { COLUMN_ID_CIRCUMSTANCE, COLUMN_ASK_CIRCUMSTANCE, COLUMN_IMAGE_CIRCUMSTANCE,
				COLUMN_ANS1_CIRCUMSTANCE, COLUMN_ANS2_CIRCUMSTANCE, COLUMN_ANS3_CIRCUMSTANCE, COLUMN_ANS4_CIRCUMSTANCE,
				COLUMN_TRUEANS_CIRCUMSTANCE, COLUMN_HIDE_CIRCUMSTANCE };
		Cursor c = db.query(TBL_CIRCUMSTANCE, columns, null, null, null, null, null);
		// getColumnIndex(COLUMN_ID_CIRCUMSTANCE); la lay chi so, vi tri cua cot COLUMN_ID_CIRCUMSTANCE
		// ...
		int iID = c.getColumnIndex(COLUMN_ID_CIRCUMSTANCE);
		int iA = c.getColumnIndex(COLUMN_ASK_CIRCUMSTANCE);
		int iI = c.getColumnIndex(COLUMN_IMAGE_CIRCUMSTANCE);
		int iA1 = c.getColumnIndex(COLUMN_ANS1_CIRCUMSTANCE);
		int iA2 = c.getColumnIndex(COLUMN_ANS2_CIRCUMSTANCE);
		int iA3 = c.getColumnIndex(COLUMN_ANS3_CIRCUMSTANCE);
		int iA4 = c.getColumnIndex(COLUMN_ANS4_CIRCUMSTANCE);
		int iT = c.getColumnIndex(COLUMN_TRUEANS_CIRCUMSTANCE);
		int iH = c.getColumnIndex(COLUMN_HIDE_CIRCUMSTANCE);

		// Vong lap lay du lieu cua con tro
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) 
		{
			docPojo = new DocPojo();
			docPojo.setId(c.getInt(iID));
			docPojo.setAsk(c.getString(iA));
			docPojo.setImg(c.getString(iI));
			docPojo.setAns1(c.getString(iA1));
			docPojo.setAns2(c.getString(iA2));
			docPojo.setAns3(c.getString(iA3));
			docPojo.setAns4(c.getString(iA4));
			docPojo.setTrueans(c.getString(iT));
			docPojo.setHide(c.getInt(iH));

			arr.add(docPojo);

		}
		c.close();
		return arr;
	}
	
	// Ham getDataUpdate tra ve toan bo du lieu cua tbl_update
	// Chuyen sang dang mang khi custom listview
	public ArrayList<DocPojo> getDataUpdate() {
		String[] columns = new String[] { COLUMN_ID_UPDATE, COLUMN_VERSION_UPDATE,
				COLUMN_DATE_UPDATE, COLUMN_DIRECT_UPDATE };
		Cursor c = db.query(TBL_UPDATE, columns, null, null, null, null, null);
		// getColumnIndex(COLUMN_ID_UPDATE); la lay chi so, vi tri cua cot COLUMN_ID_UPDATE
		// ...
		int iID = c.getColumnIndex(COLUMN_ID_UPDATE);
		int iV = c.getColumnIndex(COLUMN_VERSION_UPDATE);
		int iD = c.getColumnIndex(COLUMN_DATE_UPDATE);
		int iDI = c.getColumnIndex(COLUMN_DIRECT_UPDATE);

		// Vong lap lay du lieu cua con tro
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) 
		{
			docPojo = new DocPojo();
			docPojo.setId(c.getInt(iID));
			docPojo.setVersion(c.getInt(iV));
			docPojo.setDate(c.getString(iD));
			docPojo.setDirect(c.getString(iDI));

			arr.add(docPojo);

		}
		c.close();
		return arr;
	}
	
	// Ham getDataUpdateVersion tra ve du lieu cua cot version trong bang tbl_update
	// Tra ve gia tri la version
	public int getDataUpdateVersion() {
		int versionSqlite = 0;
		String[] columns = new String[] { COLUMN_ID_UPDATE, COLUMN_VERSION_UPDATE,
				COLUMN_DATE_UPDATE, COLUMN_DIRECT_UPDATE };
		Cursor c = db.query(TBL_UPDATE, columns, null, null, null, null, null);
		// getColumnIndex(COLUMN_ID_UPDATE); la lay chi so, vi tri cua cot COLUMN_ID_UPDATE
		// ...
		int iID = c.getColumnIndex(COLUMN_ID_UPDATE);
		int iV = c.getColumnIndex(COLUMN_VERSION_UPDATE);
		int iD = c.getColumnIndex(COLUMN_DATE_UPDATE);
		int iDI = c.getColumnIndex(COLUMN_DIRECT_UPDATE);

		// Vong lap lay du lieu cua con tro
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) 
		{
			docPojo = new DocPojo();
			docPojo.setId(c.getInt(iID));
			docPojo.setVersion(c.getInt(iV));
			docPojo.setDate(c.getString(iD));
			docPojo.setDirect(c.getString(iDI));
			versionSqlite = c.getInt(iV);

			arr.add(docPojo);

		}
		c.close();
		return versionSqlite;
	}
	
	
	
	// ---------------- class OpenHelper ------------------
	private static class OpenHelper extends SQLiteOpenHelper {
		// Ham dung khoi tao 1 OpenHelper
		public OpenHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		// Tao moi database
		@Override
		public void onCreate(SQLiteDatabase arg0) {
			
			//Tao bang tbl_theory
			arg0.execSQL("CREATE TABLE " + TBL_THEORY + " (" + COLUMN_ID_THEORY
					+ " INTEGER PRIMARY KEY, " + COLUMN_ASK_THEORY
					+ " TEXT NOT NULL, " + COLUMN_ANS1_THEORY + " TEXT NOT NULL, " + COLUMN_ANS2_THEORY
					+ " TEXT NOT NULL, " + COLUMN_ANS3_THEORY + " TEXT NOT NULL, "
					+ COLUMN_ANS4_THEORY + " TEXT, " + COLUMN_TRUEANS_THEORY
					+ " TEXT NOT NULL, " + COLUMN_HIDE_THEORY + " INTEGER NOT NULL);");
			
			//Tao bang tbl_sign
			arg0.execSQL("CREATE TABLE " + TBL_SIGN + " (" + COLUMN_ID_THEORY
					+ " INTEGER PRIMARY KEY, " + COLUMN_ASK_SIGN
					+ " TEXT NOT NULL, " + COLUMN_IMAGE_SIGN + " TEXT NOT NULL, "
					+ COLUMN_ANS1_SIGN + " TEXT NOT NULL, " + COLUMN_ANS2_SIGN
					+ " TEXT NOT NULL, " + COLUMN_ANS3_SIGN + " TEXT NOT NULL, "
					+ COLUMN_ANS4_SIGN + " TEXT, " + COLUMN_TRUEANS_SIGN
					+ " TEXT NOT NULL, " + COLUMN_HIDE_SIGN + " INTEGER NOT NULL);");
			
			//Tao bang tbl_circumstance
			arg0.execSQL("CREATE TABLE " + TBL_CIRCUMSTANCE + " (" + COLUMN_ID_CIRCUMSTANCE
					+ " INTEGER PRIMARY KEY, " + COLUMN_ASK_CIRCUMSTANCE
					+ " TEXT NOT NULL, " + COLUMN_IMAGE_CIRCUMSTANCE + " TEXT NOT NULL, "
					+ COLUMN_ANS1_CIRCUMSTANCE + " TEXT NOT NULL, " + COLUMN_ANS2_CIRCUMSTANCE
					+ " TEXT NOT NULL, " + COLUMN_ANS3_CIRCUMSTANCE + " TEXT NOT NULL, "
					+ COLUMN_ANS4_CIRCUMSTANCE + " TEXT, " + COLUMN_TRUEANS_CIRCUMSTANCE
					+ " TEXT NOT NULL, " + COLUMN_HIDE_CIRCUMSTANCE + " INTEGER NOT NULL);");
			
			//Tao bang tbl_update
			arg0.execSQL("CREATE TABLE " + TBL_UPDATE + " (" + COLUMN_ID_UPDATE
					+ " INTEGER PRIMARY KEY, " + COLUMN_VERSION_UPDATE
					+ " INTEGER NOT NULL, " + COLUMN_DATE_UPDATE + " TEXT NOT NULL, "
					+ COLUMN_DIRECT_UPDATE + " TEXT NOT NULL);");
		}

		// Kiem tra version cua database neu khac se tao lai database
		@Override
		public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
			arg0.execSQL("DROP TABLE IF EXISTS " + TBL_THEORY);
			arg0.execSQL("DROP TABLE IF EXISTS " + TBL_SIGN);
			arg0.execSQL("DROP TABLE IF EXISTS " + TBL_CIRCUMSTANCE);
			arg0.execSQL("DROP TABLE IF EXISTS " + TBL_UPDATE);
			onCreate(arg0);
		}
	}
}
